package com.gem.controller;

import com.alibaba.druid.util.StringUtils;
import com.gem.domain.*;
import com.gem.service.CCooperationResultService;
import com.gem.service.CompanyContactService;
import com.gem.service.CompanyService;
import com.gem.service.UserService;
import com.gem.util.ExcelUtil;
import com.gem.util.MessageUtil;
import com.gem.util.PageUtil;
import com.github.pagehelper.PageInfo;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
import java.util.List;

import static com.gem.util.ExcelUtil.setHSSFValidation;

/**
 * @Author: 玲
 * @Description:
 * @create 2019-10-24 14:19
 * @Modified By:
 */
@Controller
@SuppressWarnings("all")
public class CompanyController {

    @Autowired
    private CompanyService service;

    @Autowired
    private CompanyContactService contactService;

    @Autowired
    private UserService userService;

    @Autowired
    private CCooperationResultService crService;

    @RequestMapping("/company/findAll")
    public String findAll(Model model, HttpSession session, String coName, String careerClassId, String careerClassName,
                          @RequestParam(value = "currentPage", defaultValue = "1") int currentPage,
                          @RequestParam(value = "pageSize", defaultValue = "5") int pageSize) {
        User user = (User) session.getAttribute("user");
        Company company = new Company();
        model.addAttribute("coName", coName);
        model.addAttribute("careerClassId", careerClassId);
        model.addAttribute("careerClassName", careerClassName);
        if ("企培".equals(user.getJob()) && user.getAuthority() == 3) {
            company.setCoSeller(user.getUid());
        } else if ("就业".equals(user.getJob()) && user.getAuthority() == 3) {
            company.setCoCareer(user.getUid());
        }
        try {
            PageInfo<Company> pageBeans = null;
            if (user.getAuthority() != 3 && coName == null) {
                pageBeans = service.findAll(currentPage, pageSize);
            } else if (coName != null && user.getAuthority() == 3) {
                company.setCoName(coName);
                pageBeans = service.search(company, currentPage, pageSize);
            } else if (user.getAuthority() != 3 && coName != null) {
                company.setCoName(coName);
                pageBeans = service.search(company, currentPage, pageSize);
            } else {
                pageBeans = service.search(company, currentPage, pageSize);
            }
            if (pageBeans.getList() != null && pageBeans.getList().size() > 0) {
                model.addAttribute("pageBean", pageBeans);
                List<Integer> pageValue = PageUtil.getPageValue(pageBeans.getPages(), currentPage);
                model.addAttribute("pageValue", pageValue);
            } else {
                model.addAttribute("info", "没有数据请先添加！");
            }
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/company/findAll", "服务器异常！请联系管理员！", model);
            return "message";
        }
        return "company/customer";
    }

    @RequestMapping("/company/search")
    public String search(Model model, Company company,
                         @RequestParam(value = "currentPage", defaultValue = "1") int currentPage,
                         @RequestParam(value = "pageSize", defaultValue = "5") int pageSize) {
        try {
            String coName = company.getCoName();
            String sname = company.getSname();
            PageInfo<Company> pageBeans = service.search(company, currentPage, pageSize);
            if (pageBeans != null && pageBeans.getList().size() > 0) {
                System.out.println(pageBeans.getList());
                model.addAttribute("pageBean", pageBeans);
                List<Integer> pageValue = PageUtil.getPageValue(pageBeans.getPages(), currentPage);
                model.addAttribute("pageValue", pageValue);
                model.addAttribute("searchUrl", "/company/search");
                model.addAttribute("coName", coName);
                model.addAttribute("sname", sname);
            } else {
                model.addAttribute("info", "没有数据请先添加！");
            }
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/company/findAll", "服务器异常！请联系管理员！", model);
            return "message";
        }
        return "company/customer";
    }


    @RequestMapping("/company/saveUI")
    public String saveUI(Model model) {
        try {
            List<User> jmembers = userService.findJmembers();
            List<User> emembers = userService.findEmembers();
            model.addAttribute("jusers", jmembers);
            model.addAttribute("eusers", emembers);
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/company/findAll", "服务器异常！请联系管理员！", model);
            return "message";
        }
        return "company/customer_insert";
    }


    @RequestMapping("/company/save")
    @Transactional
    public String save(Company company, CompanyContact companyContact, Model model) {
        try {
            if (service.save(company, companyContact) != null) {
                MessageUtil.returnInfoPage("3", "/company/findAll", "添加成功！", model);
            } else {
                MessageUtil.returnInfoPage("3", "/company/findAll", "添加失败!(公司重名) 请联系管理员！", model);
            }
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/company/findAll", "服务器异常！请联系管理员！", model);
        }
        return "message";
    }

    @RequestMapping("/company/updateUI")
    public String updateUI(Integer coId, Model model,
                           @RequestParam(value = "currentPage", defaultValue = "1") int currentPage,
                           @RequestParam(value = "pageSize", defaultValue = "5") int pageSize) {
        try {
            Company company = service.findById(coId);
            model.addAttribute("company", company);
            model.addAttribute("currentPage", currentPage);
            model.addAttribute("pageSize", pageSize);
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/company/findAll", "服务器异常！请联系管理员！", model);
            return "message";
        }
        return "company/customer_update";
    }

    @RequestMapping("/company/update")
    public String update(Company company, Model model,
                         @RequestParam(value = "currentPage", defaultValue = "1") int currentPage,
                         @RequestParam(value = "pageSize", defaultValue = "5") int pageSize) {

        try {
            Integer result = service.update(company);
            if (result > 0) {
                MessageUtil.returnInfoPage("3", "/company/findAll?currentPage=" + currentPage + "&pageSize=" + pageSize, "修改成功！", model);
            } else {
                MessageUtil.returnInfoPage("3", "/company/findAll?currentPage=" + currentPage + "&pageSize=" + pageSize, "未作修改！", model);
            }
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/company/findAll?currentPage=" + currentPage + "&pageSize=" + pageSize, "服务器异常，请联系管理员！", model);
        } finally {
            return "message";
        }

//        return "redirect:/company/findAll?currentPage" + currentPage + "&pageSize" + pageSize;
    }

    @RequestMapping("/company/deleteById")
    @ResponseBody
    public String delete(Integer coId, int currentPageSize, int currentPage, int lastPage) {
        try {
            if (service.deleteById(coId) > 0) {
                if (currentPageSize == 1 && currentPage == lastPage) {
                    return "0";
                }
            }
            return "1";
        } catch (Exception e) {
            e.printStackTrace();
            return "-1";
        }
    }


    @RequestMapping("/company/deleteAll")
    public String deleteAll(int[] coIds, Model model) {
        try {
            if (coIds != null && coIds.length != 0) {
                service.deleteAll(coIds);
                MessageUtil.returnInfoPage("3", "/company/findAll", "删除成功", model);
            } else {
                MessageUtil.returnInfoPage("3", "/company/findAll", "没有参数传递", model);
            }
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/company/findAll", "服务器异常！请联系管理员！", model);
        } finally {
            return "message";
        }

//        return "redirect:/company/findAll";
    }


    /**
     * 其他合作成果
     */

    @RequestMapping("/cr/findOther")
    public String findOther(Integer coId, String coName, Model model, int pageNow,
                            @RequestParam(value = "currentPage", defaultValue = "1") int currentPage,
                            @RequestParam(value = "pageSize", defaultValue = "5") int pageSize) {
        model.addAttribute("coId", coId);
        model.addAttribute("coName", coName);
        model.addAttribute("pageNow", pageNow);
        try {
            PageInfo<CCooperationResult> pageBeans = crService.findOther(coId, currentPage, pageSize);
            if (pageBeans.getList() != null && pageBeans.getList().size() > 0) {
                model.addAttribute("pageBean", pageBeans);
                List<Integer> pageValue = PageUtil.getPageValue(pageBeans.getPages(), currentPage);
                model.addAttribute("pageValue", pageValue);
            } else {
                model.addAttribute("info", "没有数据请先添加！");
            }
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/company/findAll", "服务器异常！请联系管理员！", model);
            return "message";
        }
        return "company/otherResult";
    }

    @RequestMapping("/cr/saveOtherUI")
    public String saveOtherUI(Integer coId, String coName, Model model, int pageNow,
                              @RequestParam(value = "currentPage", defaultValue = "1") int currentPage,
                              @RequestParam(value = "pageSize", defaultValue = "5") int pageSize) {
        model.addAttribute("coId", coId);
        model.addAttribute("coName", coName);
        model.addAttribute("pageNow", pageNow);
        model.addAttribute("currentPage", currentPage);
        return "company/otherResult_insert";
    }

    @RequestMapping("/cr/saveOther")
    public String saveOther(Integer coId, String coName, CCooperationResult result, Model model, int pageNow,
                            @RequestParam(value = "currentPage", defaultValue = "1") int currentPage,
                            @RequestParam(value = "pageSize", defaultValue = "5") int pageSize) {
        try {
            Integer other = crService.saveOther(coId, result);
            if (other != null) {
                MessageUtil.returnInfoPage("3", "/cr/findOther?coId=" + coId + "&coName=" + coName + "&currentPage=" + currentPage + "&pageNow=" + pageNow, "添加成功!", model);
            } else {
                MessageUtil.returnInfoPage("3", "/cr/findOther?coId=" + coId + "&coName=" + coName + "&currentPage=" + currentPage + "&pageNow=" + pageNow, "添加失败!请联系管理员!", model);
            }
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/cr/findOther?coId=" + coId + "&coName=" + coName + "&currentPage=" + currentPage + "&pageNow=" + pageNow, "添加失败!请联系管理员!", model);

        }
        return "message";
    }

    @RequestMapping("/cr/deleteOtherById")
    @ResponseBody
    public String deleteOtherById(Integer crId,int currentPageSize, int currentPage, int lastPage) {
        try {
            if (crService.deleteOtherById(crId) > 0) {
                if (currentPageSize == 1 && currentPage == lastPage) {
                    return "0";
                }
            } else {
                return "-1";
            }
            return "1";
        } catch (Exception e) {
            e.printStackTrace();
            return "-1";
        }
    }

    @RequestMapping("/cr/updateOtherUI")
    public String updateOtherUI(Integer crId, Model model, Integer coId, Integer pageNow,
                                @RequestParam(value = "currentPage", defaultValue = "1") int currentPage,
                                @RequestParam(value = "pageSize", defaultValue = "5") int pageSize) {
        model.addAttribute("crId", crId);
        model.addAttribute("coId", coId);
        model.addAttribute("currentPage", currentPage);
        model.addAttribute("pageNow", pageNow);
        try {
            CCooperationResult result = crService.findOtherById(crId);
            model.addAttribute("crRemark", result.getCrRemark());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "company/otherResult_update";
    }

    @RequestMapping("/cr/updateOtherById")
    public String updateOtherById(CCooperationResult result, Integer coId, String coName, Model model, Integer pageNow,
                                  @RequestParam(value = "currentPage", defaultValue = "1") int currentPage,
                                  @RequestParam(value = "pageSize", defaultValue = "5") int pageSize) {
        try {
            crService.updateOtherById(result);
            MessageUtil.returnInfoPage("3", "/cr/findOther?coId=" + coId + "&coName=" + coName + "&currentPage=" + currentPage + "&pageNow=" + pageNow, "修改成功!", model);
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/cr/findOther?coId=" + coId + "&coName=" + coName + "&currentPage=" + currentPage + "&pageNow=" + pageNow, "修改失败/字数超出范围!请联系管理员!", model);
        }
        return "message";
    }

    @RequestMapping("/cr/deleteAllOther")
    public String deleteAllOther(int[] crIds, Model model, Integer coId, String coName, Integer pageNow,
                                 @RequestParam(value = "currentPage", defaultValue = "1") int currentPage) {
        try {
            if (crIds != null && crIds.length != 0 && crService.deleteAllOther(crIds) != null) {
                MessageUtil.returnInfoPage("3", "/cr/findOther?coId=" + coId + "&coName=" + coName + "&currentPage=" + currentPage + "&pageNow=" + pageNow, "删除成功!", model);
            } else {
                MessageUtil.returnInfoPage("3", "/cr/findOther?coId=" + coId + "&coName=" + coName + "&currentPage=" + currentPage + "&pageNow=" + pageNow, "删除失败!请联系管理员!", model);
            }
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/cr/findOther?coId=" + coId + "&coName=" + coName + "&currentPage=" + currentPage, "删除失败!请联系管理员!", model);
        } finally {
            return "message";
        }
    }


    /**
     * 就业详情带companyName-->查公司
     */
    @RequestMapping("/coCareer/findBycoName")
    public String findBycoName(Model model, String coName,
                               @RequestParam(value = "currentPage", defaultValue = "1") int currentPage,
                               @RequestParam(value = "pageSize", defaultValue = "5") int pageSize) {

        try {
            PageInfo<Company> pageBeans = service.findBycoName(currentPage, pageSize, coName);
            if (pageBeans.getList() != null && pageBeans.getList().size() > 0) {
                model.addAttribute("pageBean", pageBeans);
                List<Integer> pageValue = PageUtil.getPageValue(pageBeans.getPages(), currentPage);
                model.addAttribute("pageValue", pageValue);
            } else {
                model.addAttribute("info", "没有数据请先添加！");
            }
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/company/findAll", "服务器异常！请联系管理员！", model);
            return "message";
        }
        return "company/customer";
    }


    //导出企业excel模板
    @RequestMapping("/company/exportTemplate")
    public void exportExcelTemplate(HttpServletResponse response, HttpSession session) throws Exception {
        //创建excel工作簿对象
        HSSFWorkbook workbook = new HSSFWorkbook();

        //创建水平垂直居中的cellStyle单元格样式对象
        HSSFCellStyle cellStyle = ExcelUtil.getTitleStyle(workbook);

        //创建水平垂直居中加粗的cellStyle单元格样式,给表头试用
        HSSFCellStyle cellStyle2 = ExcelUtil.getThStyle(workbook);

        //模板第一行例子展示
        HSSFCellStyle rowStyle = ExcelUtil.getExamplStyle(workbook);

        // 单元格类型日期格式化
        HSSFCellStyle cellStyleDate = ExcelUtil.getDateRedStyle(workbook);

        //创建行对象
        HSSFRow row = null;

        //(--我的院校表--)
        //创建sheet对象
        HSSFSheet sheet = workbook.createSheet("企业客户");

        // 将第一行的三个单元格给合并,模板固定
        //注意 java的参数是从0开始 即表示excel中的一行或者第一列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
        row = sheet.createRow(0);
        HSSFCell beginCell0 = row.createCell(0);
        beginCell0.setCellValue("企业客户表");
        //cellStyle为水平居中字体加粗单元格样式
        beginCell0.setCellStyle(cellStyle);

        //第二行的单元格进行合并,语句提示
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 12));
        row = sheet.createRow(1);
        HSSFCell beginCell1 = row.createCell(0);
        beginCell1.setCellValue("请按照模板字段和单元格格式提交数据,日期类型请使用给定格式刷刷一下");
        //cellStyle为水平居中字体加粗单元格样式
        beginCell1.setCellStyle(cellStyle);

        //次数写表头,根据各个页面的进行修改
        HSSFRow headRow = sheet.createRow(2);
        headRow.createCell(0).setCellValue("企业名称");
        headRow.createCell(1).setCellValue("企业性质");
        headRow.createCell(2).setCellValue("所属行业");
        headRow.createCell(3).setCellValue("企业规模");
        headRow.createCell(4).setCellValue("企业地址");
        headRow.createCell(5).setCellValue("销售专员");
        headRow.createCell(6).setCellValue("就业专员");
        headRow.createCell(7).setCellValue("创建时间");
        headRow.createCell(8).setCellValue("备注");
        headRow.setHeightInPoints(25); // 设置行的高度
        for (int i = 0; i < 9; i++) {
            headRow.getCell(i).setCellStyle(cellStyle2);
        }

        //下拉列表的参数-
        List<User> emembers = userService.findEmembers();
        String[] eName = new String[emembers.size()];
        for (int i = 0; i < emembers.size(); i++) {
            eName[i] = emembers.get(i).getPersonname();
        }
        List<User> jmembers = userService.findJmembers();
        String[] jName = new String[jmembers.size()];
        for (int i = 0; i < jmembers.size(); i++) {
            jName[i] = jmembers.get(i).getPersonname();
        }

        User user = (User) session.getAttribute("user");
        String[] personName = {user.getPersonname()};
        if ("就业".equals(user.getJob())) {
            sheet = setHSSFValidation(sheet, eName, 3, 10000, 5, 5);
            sheet = setHSSFValidation(sheet, personName, 3, 10000, 6, 6);
        } else {
            sheet = setHSSFValidation(sheet, personName, 3, 10000, 5, 5);
            sheet = setHSSFValidation(sheet, jName, 3, 10000, 6, 6);
        }


        //优化 降低冗余
        HSSFCell cell = null;

        //设置所有列列宽
        for (int i = 0; i < 9; i++) {
            sheet.setColumnWidth(i, 20 * 256);
        } // 设置列的宽度

        //在第四行给一行数据模板,根据对应表的对应字段填写

        row = sheet.createRow(3);

        cell = row.createCell(0);
        cell.setCellStyle(rowStyle);
        cell.setCellValue("阿里巴巴");

        cell = row.createCell(1);
        cell.setCellStyle(rowStyle);
        cell.setCellValue("私企");

        cell = row.createCell(2);
        cell.setCellStyle(rowStyle);
        cell.setCellValue("信息技术");

        cell = row.createCell(3);
        cell.setCellStyle(rowStyle);
        cell.setCellValue("100人左右");

        cell = row.createCell(4);
        cell.setCellStyle(rowStyle);
        cell.setCellValue("上海市翻斗花园22号");

        cell = row.createCell(5);
        cell.setCellStyle(rowStyle);
        if ("企培".equals(user.getJob())) {
            cell.setCellValue(user.getPersonname());
        } else {
            cell.setCellValue("");
        }

        cell = row.createCell(6);
        cell.setCellStyle(rowStyle);
        if ("就业".equals(user.getJob())) {
            cell.setCellValue(user.getPersonname());
        } else {
            cell.setCellValue("");
        }

        cell = row.createCell(7);
        cell.setCellStyle(cellStyleDate);//改单元格为日期样式
        cell.setCellValue(new Date());//赋值

        cell = row.createCell(8);
        cell.setCellStyle(rowStyle);
        cell.setCellValue("没啥备注");

        OutputStream outputStream = response.getOutputStream();
        response.reset();
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;filename=template.xls");

        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }

    //导出企业客户excel
    @RequestMapping("/company/export")
    public String exportExcel(HttpServletResponse response, Model model) {
        try {
            //创建excel工作簿对象
            HSSFWorkbook workbook = new HSSFWorkbook();

            //创建水平垂直居中的cellStyle单元格样式对象
            HSSFCellStyle cellStyle = ExcelUtil.getTitleStyle(workbook);

            //创建水平垂直居中加粗的cellStyle单元格样式,给表头试用
            HSSFCellStyle cellStyle2 = ExcelUtil.getThStyle(workbook);

            // 单元格类型日期格式化
            HSSFCellStyle cellStyleDate = ExcelUtil.getDateStyle(workbook);
            //创建行对象
            HSSFRow row = null;

            //我的院校
            //创建sheet对象
            HSSFSheet sheet = workbook.createSheet("企业客户");

            // 将第一行的三个单元格给合并,模板固定
            //注意 java的参数是从0开始 即表示excel中的一行或者第一列
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
            row = sheet.createRow(0);
            HSSFCell beginCell0 = row.createCell(0);
            beginCell0.setCellValue("企业客户表");
            //cellStyle为水平居中字体加粗单元格样式
            beginCell0.setCellStyle(cellStyle);

            HSSFRow headRow = sheet.createRow(2);
            headRow.createCell(0).setCellValue("企业名称");
            headRow.createCell(1).setCellValue("企业性质");
            headRow.createCell(2).setCellValue("所属行业");
            headRow.createCell(3).setCellValue("企业规模");
            headRow.createCell(4).setCellValue("企业地址");
            headRow.createCell(5).setCellValue("销售专员");
            headRow.createCell(6).setCellValue("就业专员");
            headRow.createCell(7).setCellValue("创建时间");
            headRow.createCell(8).setCellValue("备注");
            headRow.setHeightInPoints(25); // 设置行的高度
            for (int i = 0; i < 9; i++) {
                headRow.getCell(i).setCellStyle(cellStyle2);
            }

            //创建水平垂直居中加粗的cellStyle单元格样式,给数据试用
            HSSFCellStyle rowStyle = ExcelUtil.getCenterStyle(workbook);

            //调用service方法查询所有数据集合
            List<Company> companyList = service.findAllCompany();

            //优化 降低冗余
            HSSFCell cell = null;
            Company company = null;
            //设置所有列列宽
            for (int i = 0; i < 9; i++) {
                sheet.setColumnWidth(i, 20 * 256);
            } // 设置列的宽度
            for (int i = 0; i < companyList.size(); i++) {
                row = sheet.createRow(i + 3);
                company = companyList.get(i);
                //第一列 院校名称
                cell = row.createCell(0);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(company.getCoName());

                cell = row.createCell(1);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(company.getCoType());

                cell = row.createCell(2);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(company.getCoIndustry());

                cell = row.createCell(3);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(company.getCoSize());

                cell = row.createCell(4);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(company.getCoAddress());

                cell = row.createCell(5);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(company.getSname());

                cell = row.createCell(6);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(company.getCname());

                cell = row.createCell(7);
                cell.setCellStyle(cellStyleDate);//改单元格为日期样式
                cell.setCellValue(company.getCoDate());//赋值

                cell = row.createCell(8);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(company.getCoRemarks());
            }

            //院校联系人
            //创建sheet对象
            HSSFSheet sheet2 = workbook.createSheet("企业客户联系人");
            sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
            row = sheet2.createRow(0);
            HSSFCell beginCell_20 = row.createCell(0);
            beginCell_20.setCellValue("企业客户联系人表");
            //cellStyle为水平居中字体加粗单元格样式
            beginCell_20.setCellStyle(cellStyle);

            /*次数写表头,根据各个页面的进行修改*/
            HSSFRow headRow2 = sheet2.createRow(2);
            headRow2.createCell(0).setCellValue("姓名");
            headRow2.createCell(1).setCellValue("性别");
            headRow2.createCell(2).setCellValue("职务");
            headRow2.createCell(3).setCellValue("电话");
            headRow2.createCell(4).setCellValue("QQ");
            headRow2.createCell(5).setCellValue("微信");
            headRow2.createCell(6).setCellValue("邮箱");
            headRow2.createCell(7).setCellValue("所属企业");
            headRow2.createCell(8).setCellValue("备注");
            headRow2.setHeightInPoints(25); // 设置行的高度
            for (int i = 0; i < 9; i++) {
                headRow2.getCell(i).setCellStyle(cellStyle2);
            }
            //设置所有列列宽
            for (int i = 0; i < 9; i++) {
                sheet2.setColumnWidth(i, 25 * 200);
            } // 设置列的宽度

            List<CompanyContact> contactList = contactService.findAllCon();
            CompanyContact contact = null;

            for (int i = 0; i < contactList.size(); i++) {
                row = sheet2.createRow(i + 3);
                contact = contactList.get(i);
                cell = row.createCell(0);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(contact.getCoContactName());

                cell = row.createCell(1);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(contact.getCoContactGender());

                cell = row.createCell(2);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(contact.getCoContactDuty());

                cell = row.createCell(3);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(contact.getCoContactPhone());

                cell = row.createCell(4);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(contact.getCoContactQq());

                cell = row.createCell(5);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(contact.getCoContactWechat());

                cell = row.createCell(6);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(contact.getCoContactEmail());

                cell = row.createCell(7);
                cell.setCellStyle(rowStyle);
                String companyName = null;
                try {
                    companyName = service.findById(contact.getCoContactCompanyId()).getCoName();
                } catch (Exception e) {
                    e.printStackTrace();
                    MessageUtil.returnInfoPage("3", "/company/findAll", "导出失败!请联系管理员！", model);
                    return "message";
                }
                cell.setCellValue(companyName);

                cell = row.createCell(8);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(contact.getCoContactRemarks());//赋值

            }

            OutputStream outputStream = response.getOutputStream();
            response.reset();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=Enterprise.xls");

            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/company/findAll", "导出失败!请联系管理员！", model);
            return "message";
        }
        return "redirect:/company/findAll";
    }

    //导入院校excel
    @RequestMapping("/company/import")
    @ResponseBody
    @Transactional(rollbackFor = Exception.class)
    public String importExcel(@RequestParam("file") MultipartFile file, Model model) {
        try {
            //固定格式  参数file 为 文件上传的input的name
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            HSSFSheet sheet = workbook.getSheetAt(0);

            int lastRowNum = sheet.getLastRowNum();

            Company company = null;
            HSSFRow row = null;
            User user = null;

            //从第四行开始读取数据
            for (int i = 3; i <= lastRowNum; i++) {
                company = new Company();
                row = sheet.getRow(i);

                if (StringUtils.isEmpty(row.getCell(0).getStringCellValue()) && StringUtils.isEmpty(row.getCell(1).getStringCellValue())) {
                    break;//如是空行无数据，停止写入
                }
                company.setCoName(row.getCell(0).getStringCellValue());

                company.setCoType(row.getCell(1).getStringCellValue());

                company.setCoIndustry(row.getCell(2).getStringCellValue());

                company.setCoSize(row.getCell(3).getStringCellValue());

                company.setCoAddress(row.getCell(4).getStringCellValue());
                //查询专员的uid
                user = userService.findByName(row.getCell(5).getStringCellValue());
                company.setCoSeller(user.getUid());
                user = userService.findByName(row.getCell(6).getStringCellValue());
                company.setCoCareer(user.getUid());

                Date dsDate = row.getCell(7).getDateCellValue();
                company.setCoDate(dsDate);

                company.setCoRemarks(row.getCell(8).getStringCellValue());

                //添加一个对象,循环,直到结束
                service.saveExcel(company);
            }
            return "1";
        } catch (Exception e) {
            e.printStackTrace();
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return "-1";
        }
    }
}
